﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Text.RegularExpressions;
using SoulHackerCUnite.Converters;
using SoulHackerCUnite.Data;

namespace SoulHackerCUnite.DataAdapter
{
    public class SqliteAdapter
    {
        private SQLiteConnection _connection;
        public string DataFile { get; set; }

        public SqliteAdapter()
        {

        }

        public void Open()
        {
            if (string.IsNullOrEmpty(DataFile))
            {
                DataFile = "..\\data\\soulhackersc.db3";
            }

            _connection = new SQLiteConnection();

            _connection.ConnectionString = "Data Source=" + DataFile;
            _connection.Open();
        }

        public void Close()
        {
            if (_connection != null && _connection.State != ConnectionState.Closed)
                _connection.Close();
        }

        public List<Demon> FillDemons()
        {
            List<Demon> demons = new List<Demon>();

            if (_connection.State == ConnectionState.Open)
            {
                using (SQLiteCommand comm = _connection.CreateCommand())
                {
                    comm.CommandText =
                        "SELECT [Race], [Name], [Alias]," +
                        "[Level], [HP], [MP]," +
                        "[Strength], [Intelligence]," +
                        "[Magic], [Stamina], [Agility]," +
                        "[Luck], [Disposition]," +
                        "[Convert], [StrongAndWeak1]," +
                        "[StrongAndWeak2], [StrongAndWeak3]," +
                        "[Skill1], [Skill2], [Skill3]," +
                        "[Skill4], [Skill5], [Skill6]," +
                        "[Inherit], [Other], [Race2]," +
                        "[Name2], [EnduranceFire]," +
                        "[EnduranceIce], [EnduranceLightning]," +
                        "[EnduranceImpact], [EnduranceExpel]," +
                        "[EnduranceDeath], [EnduranceJingshen]," +
                        "[EnduranceShenjing], [EnduranceMoli]," +
                        "[EnduranceDaji], [EnduranceZhanji]," +
                        "[EnduranceJi], [EnduranceGun]," +
                        "[race_j], [UnitePrec],[jname] " +
                        "FROM [demons_c_view]";
                    SQLiteDataReader reader = comm.ExecuteReader();
                    demons = TranslateDatas.TranslateData2Demons(reader);
                }
            }

            return demons;
        }

        public List<UniteRule> FillRules()
        {
            List<UniteRule> rules = new List<UniteRule>();
            if (_connection.State == ConnectionState.Open)
            {
                using (SQLiteCommand comm = _connection.CreateCommand())
                {
                    comm.CommandText =
                        "SELECT [race1], [race2], [result]," +
                        "[type] " +
                        "FROM [unite_j]";
                    SQLiteDataReader reader = comm.ExecuteReader();
                    rules = TranslateDatas.TranslateData2UniteRules(reader);
                }
            }
            return rules;
        }

        public List<Race> FillRaces()
        {
            List<Race> races = new List<Race>();
            if (_connection.State == ConnectionState.Open)
            {
                using (SQLiteCommand comm = _connection.CreateCommand())
                {
                    comm.CommandText =
                        "SELECT [RaceID], [GreatRaceID], [Race]," +
                        "[GreatRace],[UnitePrec],[Alias] " +
                        "FROM [Races]";
                    SQLiteDataReader reader = comm.ExecuteReader();
                    races = TranslateDatas.TranslateData2Race(reader);
                }
            }
            return races;
        }

        public List<Skill> FillSkills()
        {
            List<Skill> skills = new List<Skill>();
            if (_connection.State == ConnectionState.Open)
            {
                using (SQLiteCommand comm = _connection.CreateCommand())
                {
                    comm.CommandText =
                        "SELECT  [ID], [name], [MP]," +
                          "[HP], [Power], [Group]," +
                          "[Group_ID], [Target]," +
                          "[Target_Alias], [Description]," +
                          "[Alias], [Position], [jname]" +
                        "FROM [skills_c_view]";
                    SQLiteDataReader reader = comm.ExecuteReader();
                    skills = TranslateDatas.TranslateData2Skill(reader);
                }
            }
            return skills;
        }

        public List<SkillInhert> FillSkillInhert()
        {
            List<SkillInhert> skills = new List<SkillInhert>();
            if (_connection.State == ConnectionState.Open)
            {
                using (SQLiteCommand comm = _connection.CreateCommand())
                {
                    comm.CommandText =
                        "SELECT  [type], [magic], [rank]" +
                        "FROM [skills_inherit]";
                    SQLiteDataReader reader = comm.ExecuteReader();
                    skills = TranslateDatas.TranslateData2SkillInhert(reader);
                }
            }
            return skills;
        }

        public void SaveUniteToDB(List<ResultToShow> results)
        {
            if (_connection.State == ConnectionState.Open)
            {
                using (SQLiteCommand comm = _connection.CreateCommand())
                {
                    comm.CommandText = "delete from demon_unite";
                    comm.ExecuteNonQuery();

                    foreach (ResultToShow result in results)
                    {
                        comm.CommandText =
                            string.Format(
                                "insert into demon_unite (Demon1Alias,Demon2Alias,Demon3Alias,ResultAlias) values ('{0}','{1}','{2}','{3}')",
                                result.Demon1.Alias, result.Demon2.Alias,
                                result.Demon3 != null ? result.Demon3.Alias : "", result.Result.Alias);
                        comm.ExecuteNonQuery();
                    }
                }
            }
        }
    }
}
